package com.css.fxfzdpzs.modules.jcsj.repository.impl;

import com.css.fxfzdpzs.modules.jcsj.entity.HistoricalEarthquake;
import com.css.fxfzdpzs.modules.jcsj.repository.JcsjRepository;
import com.css.fxfzdpzs.modules.jcsj.vo.AreaVo;
import com.css.fxfzdpzs.modules.jcsj.vo.CompanyNumVo;
import com.css.fxfzdpzs.modules.jcsj.vo.EarthquakeNumVo;
import com.css.fxfzdpzs.modules.jcsj.vo.RoadVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class JcsjRepositoryImpl implements JcsjRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<AreaVo> getArea(String category) {
        String sql = "SELECT province ,square FROM jcsj_types_stat WHERE type ='0' AND category = '" + category + "' ORDER BY square DESC";
        List<AreaVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AreaVo.class));
        return list;
    }

    @Override
    public List<RoadVo> getLength(String category) {
        String sql = "SELECT province ,length FROM jcsj_types_stat WHERE type ='1' AND category = '" + category + "' ORDER BY length DESC";
        List<RoadVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RoadVo.class));
        return list;
    }

    @Override
    public List<CompanyNumVo> getCompanyNum(String type) {
        String sql = "SELECT province,small,middle,big FROM jcsj_types_stat WHERE type ='" + type + "'";
        List<CompanyNumVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CompanyNumVo.class));
        return list;
    }

    @Override
    public List<EarthquakeNumVo> getEarthquakeNum(String category) {
        String sql = "SELECT province ,earthquake_num FROM jcsj_types_stat WHERE type ='3' AND category = '" + category + "' ORDER BY earthquake_num DESC";
        List<EarthquakeNumVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeNumVo.class));
        return list;
    }

    @Override
    public List<HistoricalEarthquake> getEarthquakeData() {
        String sql = "SELECT * FROM jcsj_historical_earthquake";
        List<HistoricalEarthquake> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HistoricalEarthquake.class));
        return list;
    }

    @Override
    public List<EarthquakeNumVo> getEarthquakeNum1(String category, String year) {
        String sql = "SELECT province ,earthquake_num FROM jcsj_types_stat WHERE type ='3' AND category = '" + category + "' AND year = '"+year+"' ORDER BY earthquake_num DESC";
        List<EarthquakeNumVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeNumVo.class));
        return list;

    }


}
